
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import javax.swing.JOptionPane;
import javax.swing.table.DefaultTableModel;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author TQNINH
 */
class AccountManagement extends javax.swing.JFrame {

    private Connection con = null;
    private final String url = "jdbc:sqlserver://";
    private final String serverName = "localhost";
    private final String portName = "1433";
    private final String instanceName = "SQL11";
    private final String databaseName = "Account";
    private String userName = "sa";
    private String password = "";
    private String sqlStr = "";
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;
    private String strSelectedUser = "";
    /**
     * Creates new form AccountManagement
     */
//     
    public AccountManagement() {
        try {
            //<editor-fold defaultstate="collapsed" desc="Driver 1">
            /*
            //Driver 1 - 1
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            //Driver 1 - 2
            con = DriverManager.getConnection("jdbc:odbc:account", userName, password);
            */
            //</editor-fold>
            
            //<editor-fold defaultstate="collapsed" desc="Driver 4">
            //
            //Driver 4 - 1
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            //Driver 4 - 1
            con = DriverManager.getConnection(this.getConnectionUrl(), userName, password);
            //
            //</editor-fold>
            if(con != null) {
                JOptionPane.showMessageDialog(null, "Connection is successful!");
            }
        } catch(ClassNotFoundException e) {
            JOptionPane.showMessageDialog(null, "Class does not found!");
            JOptionPane.showMessageDialog(null, "Connection is not successful!");
            System.exit(0);
            e.printStackTrace();
        } catch(SQLException e) {
            if(con == null) {
                JOptionPane.showMessageDialog(null, "Connection is not successful!");
            }
            e.printStackTrace();
        }
        initComponents();
        this.fillData();
    }

    private String getConnectionUrl() {
        return url + serverName + ":" + portName + ";instanceName=" + instanceName + ";databaseName=" + databaseName;
    }
    
    private void fillData() {
        cbbUser.removeAllItems();
        try {
            sqlStr = "SELECT * FROM Login";
            //Step 2
            pstmt = con.prepareStatement(sqlStr);
            //Step 3
            rs = pstmt.executeQuery();
            while(rs.next()) {
                cbbUser.addItem(rs.getString(1));
            }
        } catch(SQLException e) {
            e.printStackTrace();
        }
    }
    
    private void displayData() {
        pstmt = null;
        rs = null;
        Vector accountData = new Vector();
        Vector<String> columnNames = new Vector();
        columnNames.add("Username");
        columnNames.add("Password");
        
        sqlStr = "SELECT * FROM Login";
        Vector<String> row = null;
        try {
            pstmt = con.prepareStatement(sqlStr);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                row = new Vector();
                row.addElement(rs.getString("username"));
                row.addElement(rs.getString("password"));
                accountData.add(row);
            }
            tblUser.setModel(new DefaultTableModel(accountData, columnNames));
            scpUser.setViewportView(tblUser);
        } catch(SQLException e) {
            e.printStackTrace();
        }
    }
    
    private void deleteData() {
        pstmt = null;
        sqlStr = null;
        if(strSelectedUser.equals("")) {
            JOptionPane.showMessageDialog(null, "SelectedUser is null!");
        } else {
            try {
                sqlStr = "SELECT * FROM Login WHERE username = '" + strSelectedUser + "'";
                //Step 2
                pstmt = con.prepareStatement(sqlStr);
                //Step 3
                rs = pstmt.executeQuery();
                if(!rs.next()) {
                    JOptionPane.showMessageDialog(null, "Username '" + strSelectedUser + "' is not exit. Please accept again!");
                } else {
                    if(JOptionPane.showConfirmDialog(null, "Delete user '" + strSelectedUser + "'", "Alert", JOptionPane.YES_NO_OPTION) == 0) {
                        try {
                            sqlStr = "DELETE FROM Login WHERE username = '" + strSelectedUser + "'";
                            //Step 2
                            pstmt = con.prepareStatement(sqlStr);
                            //Step 3
                            pstmt.executeUpdate();
                            fillData();
                            displayData();
                            JOptionPane.showMessageDialog(null, "Delete successful!");
                        } catch(SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
            } catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    private void updateData() {
        pstmt = null;
        sqlStr = null;
        String strUser = txtUsername.getText();
        String strPass = txtPassword.getText();
        if(strSelectedUser.equals("")) {
            JOptionPane.showMessageDialog(null, "SelectedUser is null!");
        } else {
            try {
                sqlStr = "SELECT * FROM Login WHERE username = '" + strSelectedUser + "'";
                //Step 2
                pstmt = con.prepareStatement(sqlStr);
                //Step 3
                rs = pstmt.executeQuery();
                if(!rs.next()) {
                    JOptionPane.showMessageDialog(null, "Username '" + strSelectedUser + "' is not exit. Please accept again!");
                } else {
                    if(JOptionPane.showConfirmDialog(null, "Update user '" + strSelectedUser + "'", "Alert", JOptionPane.YES_NO_OPTION) == 0) {
                        try {
                            sqlStr = "UPDATE Login SET "
                                    + "username = '" + strUser + "', "
                                    + "password = '" + strPass + "' "
                                    + "WHERE username = '" + strSelectedUser + "'";
                            //Step 2
                            pstmt = con.prepareStatement(sqlStr);
                            //Step 3
                            pstmt.executeUpdate();
                            fillData();
                            displayData();
                            JOptionPane.showMessageDialog(null, "Update successful!");
                        } catch(SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
            } catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    private void insertData() {
        pstmt = null;
        sqlStr = null;
        String strUser = txtUsername.getText();
        String strPass = txtPassword.getText();
        try {
            sqlStr = "SELECT * FROM Login WHERE username = '" + strUser + "'";
            //Step 2
            pstmt = con.prepareStatement(sqlStr);
            //Step 3
            rs = pstmt.executeQuery();
            if(rs.next()) {
                JOptionPane.showMessageDialog(null, "Username '" + strUser + "' da ton tai");
                txtUsername.requestFocus();
                txtUsername.selectAll();
            } else {
                if(JOptionPane.showConfirmDialog(null, "Insert user '" + strUser + "'", "Alert", JOptionPane.YES_NO_OPTION) == 0) {
                    try {
                        sqlStr = "INSERT INTO Login(username, password) VALUES('" + strUser + "', '" + strPass + "')";
                        //Step 2
                        pstmt = con.prepareStatement(sqlStr);
                        //Step 3
                        pstmt.executeUpdate();
                        fillData();
                        JOptionPane.showMessageDialog(null, "Insert successful!");
                    } catch(SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        } catch(SQLException e) {
            e.printStackTrace();
        }
    }
    
    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        lblAccountManagement = new javax.swing.JLabel();
        lblUsername = new javax.swing.JLabel();
        txtUsername = new javax.swing.JTextField();
        lblPassword = new javax.swing.JLabel();
        lblUser = new javax.swing.JLabel();
        cbbUser = new javax.swing.JComboBox();
        btnLogin = new javax.swing.JButton();
        btnInsert = new javax.swing.JButton();
        btnView = new javax.swing.JButton();
        btnFirst = new javax.swing.JButton();
        btnPrevious = new javax.swing.JButton();
        btnNext = new javax.swing.JButton();
        btnDelete = new javax.swing.JButton();
        btnUpdate = new javax.swing.JButton();
        btnClose = new javax.swing.JButton();
        btnClear = new javax.swing.JButton();
        btnLast = new javax.swing.JButton();
        scpUser = new javax.swing.JScrollPane();
        tblUser = new javax.swing.JTable();
        txtPassword = new javax.swing.JPasswordField();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        setTitle("Account Management");
        setMinimumSize(new java.awt.Dimension(465, 480));
        setResizable(false);
        getContentPane().setLayout(null);

        lblAccountManagement.setFont(new java.awt.Font("Tahoma", 1, 18)); // NOI18N
        lblAccountManagement.setHorizontalAlignment(javax.swing.SwingConstants.CENTER);
        lblAccountManagement.setText("ACCOUNT MANAGEMENT");
        lblAccountManagement.setBorder(javax.swing.BorderFactory.createEtchedBorder());
        getContentPane().add(lblAccountManagement);
        lblAccountManagement.setBounds(10, 10, 440, 50);

        lblUsername.setHorizontalAlignment(javax.swing.SwingConstants.RIGHT);
        lblUsername.setText("Username:");
        getContentPane().add(lblUsername);
        lblUsername.setBounds(20, 90, 90, 14);
        getContentPane().add(txtUsername);
        txtUsername.setBounds(130, 80, 200, 25);

        lblPassword.setHorizontalAlignment(javax.swing.SwingConstants.RIGHT);
        lblPassword.setText("Password:");
        getContentPane().add(lblPassword);
        lblPassword.setBounds(20, 130, 90, 14);

        lblUser.setHorizontalAlignment(javax.swing.SwingConstants.RIGHT);
        lblUser.setText("List of Username:");
        getContentPane().add(lblUser);
        lblUser.setBounds(20, 170, 90, 14);

        cbbUser.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                cbbUserActionPerformed(evt);
            }
        });
        getContentPane().add(cbbUser);
        cbbUser.setBounds(130, 160, 200, 25);

        btnLogin.setText("LOGIN");
        btnLogin.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnLoginActionPerformed(evt);
            }
        });
        getContentPane().add(btnLogin);
        btnLogin.setBounds(20, 200, 90, 25);

        btnInsert.setText("INSERT");
        btnInsert.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnInsertActionPerformed(evt);
            }
        });
        getContentPane().add(btnInsert);
        btnInsert.setBounds(130, 200, 90, 25);

        btnView.setText("VIEW");
        btnView.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnViewActionPerformed(evt);
            }
        });
        getContentPane().add(btnView);
        btnView.setBounds(240, 200, 90, 25);

        btnFirst.setText("FIRST");
        btnFirst.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnFirstActionPerformed(evt);
            }
        });
        getContentPane().add(btnFirst);
        btnFirst.setBounds(20, 240, 90, 25);

        btnPrevious.setText("PREVIOUS");
        btnPrevious.setEnabled(false);
        btnPrevious.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnPreviousActionPerformed(evt);
            }
        });
        getContentPane().add(btnPrevious);
        btnPrevious.setBounds(130, 240, 90, 25);

        btnNext.setText("NEXT");
        btnNext.setEnabled(false);
        btnNext.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnNextActionPerformed(evt);
            }
        });
        getContentPane().add(btnNext);
        btnNext.setBounds(240, 240, 90, 25);

        btnDelete.setText("DELETE");
        btnDelete.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnDeleteActionPerformed(evt);
            }
        });
        getContentPane().add(btnDelete);
        btnDelete.setBounds(350, 80, 90, 25);

        btnUpdate.setText("UPDATE");
        btnUpdate.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnUpdateActionPerformed(evt);
            }
        });
        getContentPane().add(btnUpdate);
        btnUpdate.setBounds(350, 120, 90, 25);

        btnClose.setText("CLOSE");
        btnClose.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnCloseActionPerformed(evt);
            }
        });
        getContentPane().add(btnClose);
        btnClose.setBounds(350, 160, 90, 25);

        btnClear.setText("CLEAR");
        btnClear.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnClearActionPerformed(evt);
            }
        });
        getContentPane().add(btnClear);
        btnClear.setBounds(350, 200, 90, 25);

        btnLast.setText("LAST");
        btnLast.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnLastActionPerformed(evt);
            }
        });
        getContentPane().add(btnLast);
        btnLast.setBounds(350, 240, 90, 25);

        tblUser.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {

            },
            new String [] {
                "Username", "Password"
            }
        ) {
            Class[] types = new Class [] {
                java.lang.String.class, java.lang.String.class
            };

            public Class getColumnClass(int columnIndex) {
                return types [columnIndex];
            }
        });
        scpUser.setViewportView(tblUser);

        getContentPane().add(scpUser);
        scpUser.setBounds(20, 285, 420, 155);
        getContentPane().add(txtPassword);
        txtPassword.setBounds(130, 120, 200, 25);

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void checkEmpty() {
        String strUser = txtUsername.getText();
        String strPass = txtPassword.getText();
        if(strUser.trim().equals("")) {
            JOptionPane.showMessageDialog(null, "Username is not blank");
            txtUsername.requestFocus();
            return;
        }
        if(strPass.trim().equals("")) {
            JOptionPane.showMessageDialog(null, "Password is not blank");
            txtPassword.requestFocus();
            return;
        }
    }
    
    private void btnLoginActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnLoginActionPerformed
        // TODO add your handling code here:
        pstmt = null;
        rs = null;
        sqlStr = null;
        String strUser = txtUsername.getText();
        String strPass = txtPassword.getText();
        checkEmpty();
        try {
            sqlStr = "SELECT * FROM Login WHERE username = '" + strUser + "' AND password = '" + strPass + "'";
            //Step 2
            pstmt = con.prepareStatement(sqlStr);
            //Step 3
            rs = pstmt.executeQuery();
            if(rs.next()) {
                JOptionPane.showMessageDialog(null, "Login is successful! Welcome " + strUser);
                strSelectedUser = strUser;
                cbbUser.setSelectedItem(strUser);
            } else {
                JOptionPane.showMessageDialog(null, "Login failed. Username or password is invalid! Please again");
            }
        } catch(SQLException e) {
            e.printStackTrace();
        }
    }//GEN-LAST:event_btnLoginActionPerformed

    private void btnInsertActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnInsertActionPerformed
        // TODO add your handling code here:
        insertData();
    }//GEN-LAST:event_btnInsertActionPerformed

    private void btnViewActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnViewActionPerformed
        // TODO add your handling code here:
        displayData();
    }//GEN-LAST:event_btnViewActionPerformed

    private void btnDeleteActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnDeleteActionPerformed
        // TODO add your handling code here:
        deleteData();
    }//GEN-LAST:event_btnDeleteActionPerformed

    private void btnUpdateActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnUpdateActionPerformed
        // TODO add your handling code here:
        updateData();
    }//GEN-LAST:event_btnUpdateActionPerformed

    private void cbbUserActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_cbbUserActionPerformed
        // TODO add your handling code here:
        
    }//GEN-LAST:event_cbbUserActionPerformed

    private void btnFirstActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnFirstActionPerformed
        // TODO add your handling code here:
        pstmt = null;
        rs = null;
        sqlStr = "SELECT * FROM Login";
        try {
            pstmt = con.prepareStatement(sqlStr, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = pstmt.executeQuery();
            rs.first();
            btnNext.setEnabled(true);
            btnPrevious.setEnabled(false);
            txtUsername.setText(rs.getString(1));
            txtPassword.setText(rs.getString(2));
        } catch(SQLException e) {
            e.printStackTrace();
        }
    }//GEN-LAST:event_btnFirstActionPerformed

    private void btnLastActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnLastActionPerformed
        // TODO add your handling code here:
        pstmt = null;
        rs = null;
        sqlStr = "SELECT * FROM Login";
        try {
            pstmt = con.prepareStatement(sqlStr, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = pstmt.executeQuery();
            rs.last();
            btnNext.setEnabled(false);
            btnPrevious.setEnabled(true);
            txtUsername.setText(rs.getString(1));
            txtPassword.setText(rs.getString(2));
        } catch(SQLException e) {
            e.printStackTrace();
        }
    }//GEN-LAST:event_btnLastActionPerformed

    private void btnPreviousActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnPreviousActionPerformed
        // TODO add your handling code here:
        try {
            rs.relative(-1);
            if(!rs.isBeforeFirst()) {
                txtUsername.setText(rs.getString(1));
                txtPassword.setText(rs.getString(2));
                btnNext.setEnabled(true);
                btnPrevious.setEnabled(true);
            } else {
                JOptionPane.showMessageDialog(null, "Dang o du lieu dau tien, khong the previous nua");
                rs.first();
                btnNext.setEnabled(true);
                btnPrevious.setEnabled(false);
            }
        } catch(SQLException e) {
            e.printStackTrace();
        }
    }//GEN-LAST:event_btnPreviousActionPerformed

    private void btnNextActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnNextActionPerformed
        // TODO add your handling code here:
        try {
            rs.relative(1);
            if(!rs.isAfterLast()) {
                txtUsername.setText(rs.getString(1));
                txtPassword.setText(rs.getString(2));
                btnNext.setEnabled(true);
                btnPrevious.setEnabled(true);
            } else {
                JOptionPane.showMessageDialog(null, "Dang o du lieu cuoi cung, khong the next nua!");
                rs.last();
                btnNext.setEnabled(false);
                btnPrevious.setEnabled(true);
            }
        } catch(SQLException e) {
            e.printStackTrace();
        }
    }//GEN-LAST:event_btnNextActionPerformed

    private void btnCloseActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnCloseActionPerformed
        // TODO add your handling code here:
        try {
            con.commit();
            if(con != null) {
                con.close();
            } else {
                con = null;
            }
        } catch(SQLException e) {
            e.printStackTrace();
        }
        System.exit(0);
    }//GEN-LAST:event_btnCloseActionPerformed

    private void btnClearActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnClearActionPerformed
        // TODO add your handling code here:
        txtUsername.setText("");
        txtPassword.setText("");
        fillData();
    }//GEN-LAST:event_btnClearActionPerformed

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        /* Set the Nimbus look and feel */
        //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
        /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
         * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html 
         */
        try {
            for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
                if ("Windows".equals(info.getName())) {
                    javax.swing.UIManager.setLookAndFeel(info.getClassName());
                    break;
                }
            }
        } catch (ClassNotFoundException | InstantiationException | IllegalAccessException | javax.swing.UnsupportedLookAndFeelException ex) {
            java.util.logging.Logger.getLogger(AccountManagement.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        }
        //</editor-fold>

        /* Create and display the form */
        java.awt.EventQueue.invokeLater(new Runnable() {
            @Override
            public void run() {
                new AccountManagement().setVisible(true);
            }
        });
    }
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton btnClear;
    private javax.swing.JButton btnClose;
    private javax.swing.JButton btnDelete;
    private javax.swing.JButton btnFirst;
    private javax.swing.JButton btnInsert;
    private javax.swing.JButton btnLast;
    private javax.swing.JButton btnLogin;
    private javax.swing.JButton btnNext;
    private javax.swing.JButton btnPrevious;
    private javax.swing.JButton btnUpdate;
    private javax.swing.JButton btnView;
    private javax.swing.JComboBox cbbUser;
    private javax.swing.JLabel lblAccountManagement;
    private javax.swing.JLabel lblPassword;
    private javax.swing.JLabel lblUser;
    private javax.swing.JLabel lblUsername;
    private javax.swing.JScrollPane scpUser;
    private javax.swing.JTable tblUser;
    private javax.swing.JPasswordField txtPassword;
    private javax.swing.JTextField txtUsername;
    // End of variables declaration//GEN-END:variables
}
